<?

/** Lo level sql routines.
 *
 *  This collection of functions (libary) is licensed under GPL2.
 *  See LICENSE or www.gnu.org for more details.
 *  @author Jyry Kuukkanen
 *  @copyright Jyry Kuukkanen
 */

include_once("sobaseclass.php");
include_once("soset.php");
include_once("sogeneric.php");

/** A wrapper for native db routines
  *
  * Usage:
  * $sql = new soSQL("dbname=megadb user=me");
  * if $sql->run($SqlClause) then happy
  * else not happy;
  *
  * Construction/setDbAccess string may contain two special key-value pairs that
  * are driver and incase: driver=pg (postgres) incase=1 (case-insensitive
  * search in where and order by clause).
  *
  * $sql->Result contains 2-D array of rows resulted from select query and each
  * row in the array has as many items as there were columns in the query,
  * e.g. "select col_a, col_b, col_c from table_x" would return y number of rows
  * each having 3 items; values for col_a, col_b and col_c.
  *
  * If $Conn == 0, then new conn is created by runSQL.
  *
  *  @package sosqllo
  */
class soSQL extends soBaseClass {


    var $DbAccess;              /** soSet Connection parameters as soSet. In
                                 *  the end these are server dependend. The
                                 *  format of the string is: "param_name=value
                                 *  another=something ..." and typival
                                 *  parameters are: database user password host
                                 *  port e.g.
                                 *  "user=me password=secret host=localhost" */
    var $Driver = "";           /** str Backend driver code to access data */
    var $__AdoDB;               /** NewADOConnection ADODB object - driver=xxx
                                 * contains the backend name */
    var $__PearDB;              /** Pear DB connection object - driver=xxx
                                 * contains the backend name */
    var $LimitCount = 0;        /** int Limits the result set to certain
                                 *  number of rows instead of all -- only works
                                 *  with SELECT. See also LimitStart */
    var $LimitStart = 0;        /** int Limits the result set starting row no.
                                 *  When set to > 0, rows read before that are
                                 *  skipped in the result -- only works with
                                 *  SELECT. This can be used in conbination
                                 *  with LimitCount to return only a range or
                                 *  rows from anywhere in the whole result set,
                                 *  such as 10 rows starting from row 20. */
    var $Trimming = 0;          /** bool Remove preceeding and trailing spaces
                                 */
    var $Transact = 0;          /** bool Use transaction when updating db */
    var $Incase = 0;            /** bool When set(1), causes where/order clause
                                 *  to be case-insensitive (assuming the driver
                                 *  permits) */
    var $DateStyle = "j.40";    /** char Date style used by db: "e"=European
                                 *  d/m/y, "j"=Japanese y/m/d or "u"=US m/d/y
                                 *  weirdness */
    var $TimeStyle = "24:s--";

    var $ConnID;                /** str connection id */
    var $Result = NULL;         /** array/array Result set from database
                                 *  (select query) */
    var $Colnames;              /** array/string Result set column names */

    /** constructor soSQL($DbAccessStr = "", $ConnID = 0)
     *  If there is some existing connection to be used,
     *  it can be passed to the class through $ConnID,
     *  in witch case $Database and $ConnParamStr can be left blank.
     *  @package sosqllo
     */
    function soSQL($DbAccessStr = "", $ConnID = 0) {
        soDebug("soSQL->constructor: dbaccess=$DbAccessStr, ".
                "connid=$ConnID", SOD_DET);

        $this->soBaseClass();
        $this->DbAccess = new soSet();

        if ($ConnID) {
            /* re-using some connection, so there is no point sorting out
               parameters for connection, then */
            $this->ConnID = $ConnID;

        } else {
            if ($DbAccessStr != "") $this->setDbAccess($DbAccessStr);

        }; // else if $ConnID

        $this->Result = array();
    } // constructor


    /** setDbAccess($DbAccessStr)
     *  Break down connection parameters from $DbAccessStr
     *  @package sosqllo
     */
    function setDbAccess($DbAccessStr) {

        /* conn.params may be space or comma delimited */
        $DbAccessStr = str_replace(",", " ", $DbAccessStr);
        $conn_params = soExplode($DbAccessStr, " ");
        $ccount = count($conn_params);

        $ado = 0;
        for ($c = 0; $c < $ccount; $c++) {
            $name = soExtractVal($conn_params[$c], "=", 1);
            $value = soExtractVal($conn_params[$c], "=", 2);
            switch ($name) {
                /* db backend, like pg for postgres, my=mysql ... */
                case "driver":
                    $this->Driver = $value;
                    break;

                case "peardb":
                    $peardb = soGetBoolValue($value);
                    break;

                case "adodb":
                    $ado = soGetBoolValue($value);
                    break;

                /* case-insensitive search and order (1=yes, 0=case-sensitive) */
                case "incase":
                    $this->Incase = soGetBoolValue($value);
                    break;

                /* trim preceeding and trailing spaces (0=no, default, 1=yes) */
                case "trimming":
                    $this->Trimming = soGetBoolValue($value);
                    break;

                /* transactions used with db (0=no, default, 1=yes) */
                case "transact":
                    $this->Transact = soGetBoolValue($value);
                    break;

                /* how does db handle dates d/m/y, y/m/d, ... */
                case "datestyle":
                case "date":
                    $this->DateStyle = $value;
                    break;

                default:
                    switch ($value) {
                        case "dbname":
                        case "db":
                            $value = "database";
                            break;
                        case "user":
                            $value = "username";
                            break;
                        case "pword":
                        case "psword":
                        case "pass":
                            $value = "password";
                    }; // switch
                    $this->DbAccess->addItem($name, $value);
            }; // switch
        }; // for

        if ($this->Driver != "") {
            #if ($this->Driver == "") $this->Driver = "pg";
            if ($ado) {
                include_once("adodb.inc.php");
                $this->__AdoDB = NewADOConnection($this->Driver);
            } else {
			    if ($peardb) {
    			    include_once("DB.php");
    			    $this->ConnID = 1;
                    $access = $this->Driver."://".
                    $this->__PearDB = DB::connect(
					    $this->Driver."://".
                        $this->DbAccess->getItem("username").":".
                        $this->DbAccess->getItem("password")."@".
						$this->DbAccess->getItem("host")."/".
                        $this->DbAccess->getItem("database"));
				} else {
                    include_once("sosql".$this->Driver.".php");
				};
            }; // else if
        };

    } // setDbAccess


    /** Executes specified clause.
     *  @param int $DebugRun When set(1), doesn't actually execute the query,
     *      but produces a debug message (debug level dependent) of the clause
     *      run.
     *  @return int Returns true(1) if the clause was successfully run (or 2
     *      when DebugRun set) or false(0) on error.
     *  @package sosqllo
     */
    function run($Clause, $DebugRun = 0) {

        if ($this->Driver == "") {
            soDebug("soSQL->run: Driver missing!", SOD_DET);
            $this->Error = 1;
            return 0; // <-- EXIT
        }; // if

        $this->Result = NULL;
        if (!$this->ConnID) {
            if ($this->__AdoDB) {
                $this->ConnID =
                    $this->__AdoDB->Connect(
                        $this->DbAccess->getItem("host"),
                        $this->DbAccess->getItem("user"),
                        $this->DbAccess->getItem("password"),
                        $this->DbAccess->getItem("database"));
            } else {
                if (!$this->__PearDB) {
                    eval("\$this->ConnID = soOpenDbConn".$this->Driver.
                         "(\$this->DbAccess);");
				};
            }; // else if
        }; // if

        if (!$this->ConnID) {
            $this->Error = 1;
            soDebug("soOpenDbConn(".$this->Driver.")->options='".$connstr.
                    "' return=0", SOD_HI);
            return 0; // <-- EXIT
        }; // if

        /* no sql clause to run or connection to db not open? */
        if ($Clause == "") {
            $this->Error = 1;
            return 0; // <-- EXIT
        };

        if ($DebugRun) {
            $result = 2;
            soDebug("soSQL->debugrun: $Clause", SOD_DET);
        } else {
            if ($this->__AdoDB) {
                $result = $this->__AdoDB->Execute($Clause);
                if ($result) {
                    $this->Result = $result->GetArray();
                    $result = 1;
                    soDebug("soSQL->run(ado): $Clause", SOD_DET);
                } else {
                    soDebug("soSQL->run: ERROR '".$Clause."' ".
                            $this->__AdoDB->ErrorMsg(), SOD_HI);
                };
            } else {
                if ($this->__PearDB) {
				    $result = $this->__PearDB->query($Clause);
                    if (!DB::isError($result)) {
                        $this->Result = array();
                        if ($result->numRows($result)) {
                            $vals = $result->fetchRow(DB_FETCHMODE_ASSOC);
                            if (gettype($vals) == "array") {
                                $keys = array_keys($vals);
                                foreach ($keys as $key) {
                                    if (!soIsNum($key)) {
                                        $this->ColNames[] = $key;
                                    }; // if
                                }; // foreach
                            }; // if
                        }; // if
                        while ($row = $result->fetchRow()) {
                            $this->Result[] = $row;
                        }; // while
                    };
				} else {
                    @eval("\$result = (soRunSqlClause".$this->Driver.
                          "(\$this->ConnID, \$Clause,
                           \$this->Result, \$this->LimitCount,
                           \$this->LimitStart) != 0);");
                };
            }; // else if
        };
        soDebug("soSQL->run: result=$result, datarows=".count($this->Result), SOD_DET);

        $this->Error = ($result == 0);
        return $result;
    } // run
    
    
    function getColNames() {
        return $this->ColNames;
    } // getColNames


	/** Closes connection to db.
	 */
	function disconnect() {
	    if ($this->__PearDB) {
		    $this->__PearDB->disconnect();
		} else {
            if ($this->__AdoDB) {
	    	} else {
    		};
		};
	} // disconnect

}; // soSQL

?>
